package com.aiflow.Action;

import global.Constants;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import com.aiflow.db.ConnDB;
import com.aiflow.util.DownExcell;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

//  告警查询 
public class AlarmQueryAction {

	// 查询 告警信息
	public void findAlarm() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		String region = (String) req.getParameter("region");
		String startTime = (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String alarmTitle = (String) req.getParameter("alarmTitle");
		String alarmStatus = null;
		if (startTime != null && !startTime.equals("")) {
			startTime = (startTime.split("T"))[0];
		}
		if (endTime != null && !endTime.equals("")) {
			endTime = (endTime.split("T"))[0];
		}

		if (((String) req.getParameter("alarmStatus")).equals("")) {
			alarmStatus = "1";
		} else if(((String) req.getParameter("alarmStatus")).equals("-1")){
			alarmStatus = "";
		}else {
			alarmStatus = (String) req.getParameter("alarmStatus");
		}
		int end = start + limit;
		int begin = start + 1;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if (region != null && !region.equals("")) {
			if (region.equals(Constants.netManager)) {
				whereSql += " and   Region_name = '' ";
			} else {
				whereSql += " and   Region_name like '%" + region + "%' ";
			}
		}
		if (alarmStatus != null && !alarmStatus.equals("")) {
			whereSql += " and  Alarm_status = '" + alarmStatus + "' ";
		}
		if (startTime != null && !startTime.equals("")) {
			whereSql += " and  Alarm_time >= '" + startTime + "' ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  Alarm_time < '" + endTime + " 23:59:59' ";
		}
		if (alarmTitle != null && !alarmTitle.equals("")) {
			whereSql += " and  Alarm_title like '%" + alarmTitle + "%' ";
		}
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		try {
//			String sql = "select * from "
//					+ "("
//					+ "select id,Alarm_id,case when Region_name = '' then '网管中心' else Region_name end as Region_name,Alarm_pro,Dn,Dn_type,Alarm_time,Org_severity,Location,Alarm_title, alarm_status,alarm_type,  "
//					+ " (row_number() over(ORDER BY id desc)) rb "
//					+ " from Alarm_info  where 1=1 " + whereSql + ") tt "
//					+ "where tt.rb between " + begin + " and " + end;
			
			String sql="  select * from (" +
			        " select id,Alarm_id,case when Region_name = '' then '网管中心' else Region_name end as Region_name, " +
					" Alarm_pro,Dn,Dn_type,Alarm_time,Org_severity,Location,Alarm_title, alarm_status,alarm_type, " +
					" (row_number() over(ORDER BY id desc)) rb,snumbe  from Alarm_info  where 1=1 "+ whereSql +" )tt " +
					" where tt.rb "+
					" between " + begin + " and " + end;

			sqlCount = "select count(1) " + " from Alarm_info where 1=1 "
					+ whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("Alarm_id", rs.getString(2));
				mapdata.put("Region_name", rs.getString(3));
				mapdata.put("Alarm_pro", rs.getString(4));
				mapdata.put("Dn", rs.getString(5));
				mapdata.put("Dn_type", rs.getString(6));
				mapdata.put("Alarm_time", rs.getString(7));
				mapdata.put("Alarm_title", rs.getString(10));
				mapdata.put("alarm_status", rs.getString(11));
				mapdata.put("alarm_type", rs.getString(12));
				mapdata.put("snumbe", rs.getString(14));

				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();

			}
		}

	}

	public void saveAlarm() { // ,alarm_type= ?
		ConnDB db = new ConnDB();
		Connection con = db.getConnection();
		String[] alarmType = null;
		try {
			con.setAutoCommit(false);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement ps = null;
		PreparedStatement psType = null;
		String sql = " update Alarm_info set alarm_status =? where Alarm_id=?";

		String typeSql = " update Alarm_info set alarm_type = ? where  alarm_title = ( "
				+ " select alarm_title from Alarm_info where Alarm_id = ? )";

		HttpServletRequest req = ServletActionContext.getRequest();
		String arr = req.getParameter("jsonArr");
		String val = req.getParameter("val");
		JSONArray jsonArray = JSONArray.fromObject(arr);
		if (val != null && !val.equals("")) {
			alarmType = val.split(",");
		}

		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < jsonArray.size(); i++) {
				JSONObject obj = jsonArray.getJSONObject(i);
				String alarm_status = obj.getString("alarm_status"); // 告警类型
				//  
				String alarm_id = obj.getString("Alarm_id");
				ps.setString(1, alarm_status);
				// ps.setString(2, alarm_type);
				ps.setString(2, alarm_id);
				ps.addBatch();

			}
			psType = con.prepareStatement(typeSql);
			if (alarmType != null && alarmType.length > 0) {
				for (int j = 0; j < alarmType.length; j++) {
					int w = Integer.parseInt(alarmType[j]);
					JSONObject obj = jsonArray.getJSONObject(w);
					String alarm_Id = obj.getString("Alarm_id"); // 告警状态
																	// (一级告警 。。
																	// )
					String alarm_type = obj.getString("alarm_type"); // 告警状态
																		// (一级告警
																		// 。。 )
					psType.setString(1, alarm_type);
					psType.setString(2, alarm_Id);
					psType.addBatch();
				}
			}

			int[] count = ps.executeBatch();
			int[] countType = psType.executeBatch();
			con.commit();
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			db.closeStatement(ps);
			db.closeStatement(psType);
			db.closeConnect(con);

		}

	}

	// 删除 字典表数据
	public void deleteAlarm() {
		ConnDB db = new ConnDB();
		Statement ps = null;
		HttpServletRequest req = ServletActionContext.getRequest();
		String id = req.getParameter("alarmIds");

		String sqld = "";
		id = id.substring(0, id.length() - 1);
		Connection con = db.getConnection();
		try {
			con.setAutoCommit(false);
			ps = con.createStatement();

			boolean bool = false;

			String sql = "delete Alarm_info  where Alarm_id in( " + id + " )";
			bool = ps.execute(sql);
			ps = con.createStatement();
			if (bool) {
				// System.out.println("保存成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				con.commit();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public void down() {
		HttpServletRequest req = ServletActionContext.getRequest();
		HttpServletResponse res = ServletActionContext.getResponse();
		String region = (String) req.getParameter("region");
		String alarmStatus = (String) req.getParameter("alarmStatus");
		String whereSql = "";
		if (region != null && !region.equals("")) {
			if (region.equals(Constants.netManager)) {
				whereSql += " and   Region_name = '' ";
			} else {
				whereSql += " and   Region_name like '%" + region + "%' ";
			}
		}
		if (alarmStatus != null && !alarmStatus.equals("") && !alarmStatus.equals("-1")) {
			whereSql += " and  Alarm_status = '" + alarmStatus + "' ";
		}
		String sql = " select Alarm_id 告警编号 ,case when Region_name = '' then '网管中心' else Region_name end as 区分公司,Alarm_pro as  告警专业,Dn 网元名称,Dn_type 网元名称,Alarm_time 告警时间,Alarm_title 告警标题, case alarm_status when 0 then '已恢复' else '未恢复' end as  告警状态  "
				+ " from Alarm_info  where 1=1 " + whereSql;
		DownExcell excel = new DownExcell();
		excel.getData(sql, req, res, "告警记录查询");
	}
	
	//导出督办工单的Excel
	public void supervisedown() {
		HttpServletRequest req = ServletActionContext.getRequest();
		HttpServletResponse res = ServletActionContext.getResponse();
		String region = (String) req.getParameter("region");
		String superviseStatus = (String) req.getParameter("superviseStatus");
		String startTime = req.getParameter("startTime");
		String endTime = req.getParameter("endTime");
		String whereSql = "";
		if (startTime != null && !startTime.equals("")) {
			whereSql += " and  Supervise_time >= '" + startTime + "' ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  Supervise_time < '" + endTime + " 23:59:59' ";
		}
		if (region != null && !region.equals("")) {
			if (region.equals(Constants.netManager)) {
				whereSql += " and   Region_name = '' ";
			} else {
				whereSql += " and   Region_name like '%" + region + "%' ";
			}
		}
		if (superviseStatus != null && !superviseStatus.equals("") && !superviseStatus.equals("-1")) {
			whereSql += " and  Supervise_status = '" + superviseStatus + "' ";
		}
		String sql = " select Supervise_id 工单编号 ,Region_name 区分公司,Supervise_time 派单时间,Supervise_user 派单对象,work_level 工单级别,work_title 工单标题,work_time 工单历时,wy 网元,case Supervise_status when 770 then '受理' when 771 then '回复' when 773 then '实时督办' else '过程' end as 工单类型,case send when 0 then '已恢复' else '未恢复' end as  工单状态  "
				+ " from Supervise_info  where 1=1 " + whereSql;
		DownExcell excel = new DownExcell();
		excel.getData(sql, req, res, "督办工单记录查询");
	}
	
	//导出督办人员配置的Excel模板
	public void vildown() {
		HttpServletRequest req = ServletActionContext.getRequest();
		HttpServletResponse res = ServletActionContext.getResponse();
		String sql = "select a.user_name 派单对象,a.telep 电话号码,z.name 督办级别,d.dept_name 地市,a.sort 序列号,'' [注意：下面内容都是必填项,在填写内容时,请注意按照模板内例子来填写,派单对象(班组-名字),督办级别(IVR督办人,IVR一次升级督办人···)]"+
                     " from t_s_personConfigure a,department d,dictionary z where d.id = a.region and z.id = superviseTarget and a.user_name like '%宜昌市网优中心项目优化班%'";
		DownExcell excel = new DownExcell();
		excel.getData(sql, req, res, "督办人员配置模板");
	}
	
	//导出督办人员配置的Excel
	public void persondown() {
		HttpServletRequest req = ServletActionContext.getRequest();
		HttpServletResponse res = ServletActionContext.getResponse();
		String region = req.getParameter("region");
		String userName = req.getParameter("userName");
		String whereSql = "";
		if (region != null && !region.equals("")) {
			whereSql += " and  region = " + region;
		}
		if (userName != null && !userName.equals("")) {
			whereSql += " and  user_name like '%" + userName + "%' ";
		}
		String sql = "select user_name 派单对象 ,telep 手机号,z.name 督办级别,d.dept_name 地市"
				+ " from t_s_personConfigure a,department d,dictionary z where d.id = a.region and z.id = superviseTarget " + whereSql;
		DownExcell excel = new DownExcell();
		excel.getData(sql, req, res, "督办工单记录查询");
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
